from openpyxl import Workbook, load_workbook

# 加载原始工作簿
original_workbook = load_workbook(filename='d://中心车辆台账6.18.xlsx')

# 创建一个字典来存储每个Sheet的最大值
max_values = {}

# 遍历所有Sheet
for sheet_name in original_workbook.sheetnames:
    sheet = original_workbook[sheet_name]
    
    # 初始化最大值为None，以便能处理空列的情况
    max_value = None
    
    # 遍历G列的每一个单元格
    for row in sheet.iter_rows(min_row=1, min_col=7, max_col=7, values_only=True):
        cell_value = row[0]
        
        # 跳过空单元格
        if cell_value is None:
            continue
        
        # 尝试将单元格值转换为整数
        try:
            cell_value = int(cell_value)
        except (ValueError, TypeError):
            # 如果转换失败，跳过该单元格
            continue
        
        # 更新最大值
        if max_value is None or cell_value > max_value:
            max_value = cell_value
            
    # 存储每个Sheet的最大值
    max_values[sheet_name] = max_value

# 创建一个新的工作簿
new_workbook = Workbook()
new_sheet = new_workbook.active
new_sheet.title = "最大值汇总"

# 写入表头
new_sheet['A1'] = "Sheet名称"
new_sheet['B1'] = "最大值"

# 写入数据
row = 2
for sheet_name, max_value in max_values.items():
    new_sheet[f'A{row}'] = sheet_name
    new_sheet[f'B{row}'] = max_value
    row += 1

# 保存新工作簿
new_workbook.save(filename='d://中心车辆台账6.18_results.xlsx')

print("结果已保存到 d://中心车辆台账6.18_results.xlsx")